package udl;

import model.Road;
import tool.Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OpreationRoad {
    Util util = new Util();
    Connection connection=null;
    OpreationAttraction opreationAttraction;
    //添加路线
    public void addRoad(String text1, String text2,int text3) {
        connection=util.getConnection();
        opreationAttraction=new OpreationAttraction();
        String sql = "insert into road(start, end, start_x, start_y, end_x, end_y, distance) values(?,?,?,?,?,?,?)";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, text1);
            preparedStatement.setString(2, text2);
            if(opreationAttraction!=null){
                preparedStatement.setInt(3, opreationAttraction.selectX(text1));
                preparedStatement.setInt(4, opreationAttraction.selectY(text1));
                preparedStatement.setInt(5, opreationAttraction.selectX(text2));
                preparedStatement.setInt(6, opreationAttraction.selectY(text2));
            }
            preparedStatement.setInt(7, text3);
            preparedStatement.executeUpdate();
            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //返回记录路线条数
    public int returnnum1() {
        int num=0;
        connection=util.getConnection();
        String sql="select * from road";
        try{

            PreparedStatement ptmt=connection.prepareStatement(sql);
            ResultSet res=ptmt.executeQuery();
            while(res.next())
            {
                num++;
            }
        }catch(Exception  e)
        {
            e.printStackTrace();
        }
        return num;
    }
    //删除一个景点
    public boolean deleteView(String string) {
        connection=util.getConnection();
        String sql = "delete from attraction where name like '%"+string+"%'";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();
            deleteRoad1(string);
            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return true;
    }
    //删除路线
    public void deleteRoad1(String string){
        connection=util.getConnection();
        String sql = "delete from road where start=? or end=?";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, string);
            preparedStatement.setString(2, string);
            preparedStatement.executeUpdate();
            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //查找所有路线信息
    public List<Road> selectViews1() {
        connection=util.getConnection();
        String sql="select * from road";
        List<Road> list=null;
        Road road;
        try{
            list=new ArrayList<>();

            PreparedStatement ptmt=connection.prepareStatement(sql);
            ResultSet  res=ptmt.executeQuery();
            while(res.next())
            {
                road=new Road();
                road.setId(res.getInt("id"));
                road.setStartRoad(res.getString("start"));
                road.setEndRoad(res.getString("end"));
                road.setDistance(res.getInt("distance"));
                road.setStartX(res.getInt("start_x"));
                road.setStartY(res.getInt("start_y"));
                road.setEndX(res.getInt("end_x"));
                road.setEndY(res.getInt("end_y"));
                list.add(road);
            }
        }catch(Exception  e)
        {
            e.printStackTrace();
        }

        return list;
    }
    //是否存在路线
    public boolean ifExistRoad(String text1, String text2) {
        connection=util.getConnection();
        String sql = "select * from road where (start =? and end =?)or(start =? and end =?)";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, text1);
            preparedStatement.setString(2, text2);
            preparedStatement.setString(3, text2);
            preparedStatement.setString(4,text1);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                return true;
            }
            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return false;
    }
    //删除路径
    public void deleteRoad(String text1, String text2) {
        connection=util.getConnection();
        String sql="delete from road where (start=? and end= ?) or (start = ? and end = ?)";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,text1);
            preparedStatement.setString(2,text2);
            preparedStatement.setString(3,text2);
            preparedStatement.setString(4,text1);
            preparedStatement.executeUpdate();
            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //修改路线
    public void modifyRoad(String text1, String text2, Integer text3) {
        connection=util.getConnection();
        String sql = "update road set distance= ? where (start=? and end =?)or(start =? and end =?)";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,text3);
            preparedStatement.setString(2,text1);
            preparedStatement.setString(3,text2);
            preparedStatement.setString(4,text2);
            preparedStatement.setString(5,text1);
            preparedStatement.executeUpdate();
            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
